package cytest.embedded;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component
public class H2CommandProcessor {

  @Autowired
  JdbcTemplate jdbcTemplate;

  private static String tableSQL = "CREATE TABLE IF NOT EXISTS `base_code` ("
      + "  `id` bigint(20) unsigned NOT NULL COMMENT '逻辑主键',   "
      + "  `createdate` datetime NOT NULL COMMENT '创建日期', "
      + "  `modifydate` datetime NOT NULL COMMENT '修改日期', "
      + "  `channel` int(4) unsigned NOT NULL DEFAULT '1' COMMENT '注册渠道（1001食尚购）', "
      + "  `disabledflag` bit(1) NOT NULL DEFAULT '0' COMMENT '禁用标志（0否 1是）', "
      + "  `idleflag` bit(1) NOT NULL DEFAULT '0' COMMENT '空闲标志（0否 1是）', "
      + "  `syscode` bigint(20) unsigned NOT NULL COMMENT '系统唯一号', "
      + "  `sysdemo` varchar(200) NOT NULL DEFAULT '' COMMENT '系统备注', "
      + "  `sellerflag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否开通卖家身份_集团开通（0否 1已申请 2已开通 ）', "
      + "  `besellerdate` datetime DEFAULT NULL COMMENT '申请开通卖家时间', "
      + "  `tpsystem` int(4) NOT NULL DEFAULT '0' COMMENT '第三方系统', "
      + "  PRIMARY KEY (`id`),"
      + "  KEY `syscode` (`syscode`)"
      + ") COMMENT='开通系统唯一码记录表' ";
  
  public void initPassword() {
    jdbcTemplate.execute("ALTER USER sa SET { PASSWORD 'ilt2' }");
  }

  /**
   * 创建表格.
   */
  public void create() {
    jdbcTemplate.execute("CREATE TABLE  IF NOT EXISTS USER "
        + "(ID VARCHAR2(128),NAME VARCHAR2(128),AGE NUMBER(3), SCORE NUMBER(12,4))");
    jdbcTemplate.execute(tableSQL);
  }

  public void insert(User user) {
    String sql = "INSERT INTO USER VALUES(?, ?, ?,?)";
    jdbcTemplate.update(sql, new Object[] { user.getId(), user.getName(), user.getAge(), 89.75d });
  }

  /**
   * 查询接口.
   * @param id .
   * @return .
   */
  public User query(String id) {
    final User user = new User();
    String sql = String.format("SELECT ID, NAME, AGE FROM USER WHERE ID = ?");
    jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
        public void processRow(ResultSet rs) throws SQLException {
        user.setId(rs.getString("id"));
            user.setName(rs.getString("name"));
            user.setAge(rs.getInt("age"));
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
              System.out.println(rs.getMetaData().getColumnName(i));
              System.out.println(rs.getMetaData().getColumnTypeName(i));
              System.out.println(rs.getMetaData().getColumnType(i));
            }
        }
    });
    return user;
  }

}
